﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class FLTDYEORDERData : BaseFunction
    {
        
        public FLTDYEORDERData()
            : base()
        {

        }
        public FLTDYEORDERData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetVKPOS(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "DYEORDER";
            string ORDER = dwABPOS["ORDER"].ToString().Trim();
            string CO = dwABPOS["CO"].ToString().Trim();
            string FACTORY = dwABPOS["FACTORY"].ToString().Trim();
            string FROMDATE = dwABPOS["fromdate"].ToString().Trim();
            string ENDDATE = dwABPOS["enddate"].ToString().Trim();
            string STATUS = dwABPOS["STATUS"].ToString().Trim();           
            
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = " SELECT DECODE(A.ABPERLKZ,'J','FINISHED') AS ABPERLKZ,A.ABPANRKR,A.ABPAUFKRS, "
                    + " TO_NUMBER(A.ABPAUFLFD) AS FLFD,TO_NUMBER(A.ABPAUFPOS) AS FPOS,B.ABKKDAUFTNR AS CO,E.VKPLIEFTERM, "
                    + " E.MER,E.VKPART,E.VKPFARBE,E.REMARK,A.ABPSOLLMENGE5/100 AS ORDQTY,A.ABPSTARTDAT, "
                    + " A.ABPLIEFDAT,C.ABAKDPARTIE,C.ABARESMG/1000 AS BATQTY,C.ABAKDAUFDAT, "
                    + " C.BEM,C.ABANEUDAT,C.STATUS,A.ABPZUGKG/100 as ALLQTY, "
                    + " D.ZUNEUDAT AS ALLDATE,D.DN,D.ZUSPPART  AS SPPART,D.VVVERSDAT AS ISSDATE,D.DNQTY AS DNQTY "
                    + " FROM ABPOS A,ABKOPF B, "
                    + " (SELECT C.ABAMANDANT,C.ABAAUFKRS,C.ABAAUFLFD,C.ABAAUFPOS,C.ABAKDAUFDAT, "
                    + " TRIM(C.ABABEM1)||TRIM(C.ABABEM2) AS BEM, "
                    + " C.ABAKDPARTIE,C.ABARESMG,C.ABANEUDAT,TRIM(SUBSTR(C.ABAKDAUFTNR,9,2)) AS STATUS "
                    + " FROM ABAUFM C WHERE C.ABAAUFTYP = 'V' "
                    + " AND C.ABAAPOS>'0001' AND C.ABAVKAUFTYP = ' ') C, "
                    + " (SELECT DISTINCT Z.ZUMANDANT,Z.ZUAUFKRS,Z.ZUAUFLFD,Z.ZUAUFPOS, "
                    + " SUM(Z.ZUEINTKG / 1000)OVER(PARTITION BY Z.ZUMANDANT,Z.ZUAUFKRS,Z.ZUAUFLFD,Z.ZUAUFPOS) AS ALLQTY, "
                    + " stragg(distinct Z.ZUNEUDAT)OVER(PARTITION BY Z.ZUMANDANT, Z.ZUAUFKRS, Z.ZUAUFLFD, Z.ZUAUFPOS) as ZUNEUDAT, "
                    + " STRAGG(DISTINCT TO_NUMBER(B.GABAUFKRS || B.GABAUFLFD))OVER(PARTITION BY Z.ZUMANDANT, "
                    + " Z.ZUAUFKRS,Z.ZUAUFLFD,Z.ZUAUFPOS) AS DN, "
                    + " STRAGG(DISTINCT Z.ZUSPPART)OVER(PARTITION BY Z.ZUMANDANT,Z.ZUAUFKRS,Z.ZUAUFLFD,Z.ZUAUFPOS) AS ZUSPPART, "
                    + " STRAGG(DISTINCT C.VVVERSDAT)OVER(PARTITION BY Z.ZUMANDANT,Z.ZUAUFKRS,Z.ZUAUFLFD,Z.ZUAUFPOS) AS VVVERSDAT, "
                    + " SUM(DISTINCT C.KG)OVER(PARTITION BY Z.ZUMANDANT,Z.ZUAUFKRS,Z.ZUAUFLFD,Z.ZUAUFPOS) AS DNQTY "
                    + " FROM ZUTEI Z,GABEW A,GABEW B,(SELECT VVTMANDANT,VVVERSDAT,SUBSTR(VVTART,1,20) AS VVTART, "
                    + " VVTVAR,VVTFARBE,VVTKG/1000 AS KG,VVTLIEFSCH,VVTLIEFSCHPOS,V2.VVTAUFKRS,V2.VVTAUFLFD,V2.VVTAUFPOS "
                    + " FROM VVLIEF V1,VVTLIEF V2 WHERE V1.VVMANDANT = V2.VVTMANDANT AND V1.VVLIEFSCH = V2.VVTLIEFSCH ) C "
                    + " WHERE Z.ZUMANDANT = B.GABMANDANT AND Z.ZUPARTIE = B.GABPARTIE "
                    + " AND Z.ZUPARTIEKZ = B.GABPARTIEKZ AND Z.ZULBEHI = B.GABLBEHI "
                    + " AND B.GABMANDANT = A.GABMANDANT AND B.GABJJJJ = A.GABJJJJ "
                    + " AND B.GABBELEG = A.GABBELEG  AND B.GABPARTIE = A.GABPARTIE AND B.GABPARTIEKZ = A.GABPARTIEKZ "
                    + " AND B.GABLFDNR = A.GABLFDNR+1 AND B.GABFC = A.GABFC AND B.GABMANDANT = C.VVTMANDANT "
                    + " AND B.GABART = C.VVTART AND B.GABVAR = C.VVTVAR AND B.GABFARBE = C.VVTFARBE "
                    + " AND B.GABAUFKRS = C.VVTAUFKRS AND B.GABAUFLFD = C.VVTAUFLFD AND B.GABAUFPOS = C.VVTAUFPOS "
                    + " AND A.GABBS = '20' AND B.GABBS = '21' AND B.GABFC = 'VV20' AND Z.ZUFLAG = 'S') D, "
                    + " (SELECT A.ABAMANDANT,A.ABAAUFKRS,A.ABAAUFLFD,A.ABAAUFPOS, "
                    + " STRAGG(B.VKPNEUBEN)OVER(PARTITION BY A.ABAAUFKRS,A.ABAAUFLFD,A.ABAAUFPOS) AS MER, "
                    + " B.VKPART,B.VKPFARBE,B.VKPLIEFTERM, "
                    + " STRAGG(DISTINCT TRIM(B.VKPKDBEZ))OVER(PARTITION BY A.ABAAUFKRS,A.ABAAUFLFD,A.ABAAUFPOS) AS REMARK "
                    + " FROM ABAUFM A,VKPOS B "
                    + " WHERE A.ABAMANDANT = B.VKPMANDANT AND A.ABAVKAUFKRS = B.VKPAUFKRS "
                    + " AND A.ABAVKAUFLFD = B.VKPAUFLFD AND A.ABAVKAUFPOS = B.VKPAUFPOS "
                    + " AND A.ABAVKAUFTYP = 'K' AND B.VKPSA <>'AJ') E "
                    + " WHERE A.ABPMANDANT = B.ABKMANDANT AND A.ABPAUFKRS = B.ABKAUFKRS "
                    + " AND A.ABPAUFLFD = B.ABKAUFLFD AND A.ABPMANDANT = C.ABAMANDANT(+) "
                    + " AND A.ABPAUFKRS = C.ABAAUFKRS(+) AND A.ABPAUFLFD = C.ABAAUFLFD(+) "
                    + " AND A.ABPAUFPOS = C.ABAAUFPOS(+) AND A.ABPMANDANT = D.ZUMANDANT(+) AND A.ABPAUFKRS = D.ZUAUFKRS(+) "
                    + " AND A.ABPAUFLFD = D.ZUAUFLFD(+) AND A.ABPAUFPOS = D.ZUAUFPOS(+) "
                    + " AND A.ABPMANDANT = E.ABAMANDANT(+) AND A.ABPAUFKRS = E.ABAAUFKRS(+) AND A.ABPAUFLFD = E.ABAAUFLFD(+) "
                    + " AND A.ABPAUFPOS = E.ABAAUFPOS(+) AND A.ABPAUFKRS = 'D7' AND A.ABPLOEKZ <>'J' "
                    + " AND (A.ABPAUFLFD = '" + ORDER + "' OR '" + ORDER + "' = '000000') "
                    + " AND A.ABPLIEFDAT >='" + FROMDATE + "' AND A.ABPLIEFDAT <= '" + ENDDATE + "' " 
                    + " AND (A.ABPANRKR ='" + FACTORY + "' OR '" + FACTORY + "' = 'AA') "
                    + " AND (A.ABPERLKZ = ' ' OR '" + STATUS + "' = 'A') "
                    + " AND TRIM(B.ABKKDAUFTNR) LIKE '%" + CO + "%'"
                    + " ORDER BY 2,3,4 "                   
                    ;
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["REMARK"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["REMARK"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["BEM"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["BEM"] = value;
                    }
                }                
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }        
        public DataSet BuildFLTDYEORDERData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVKPOS(dwUI);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        
    }
}